Retrieving Database Records Using ResultSet in Java JDBC

Course: Advanced Java Training

Topic: Retrieving Database Records Using ResultSet

Course Link: Advanced Java Training Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to ResultSet in JDBC

Java Database Connectivity (JDBC) is an API that enables Java applications to connect to relational databases, execute SQL queries, retrieve records, and perform database operations. JDBC provides interfaces such as Connection, Statement, PreparedStatement, and ResultSet to support database interaction.

The ResultSet interface belongs to the java.sql package. It represents tabular data returned by executing a query against a database. A ResultSet contains rows and columns, allowing a Java application to access database records one row at a time.

For example, when a Java application executes a SELECT query to retrieve student information, the database returns the matching records through a ResultSet. The application can then read student IDs, names, courses, and marks using appropriate getter methods.

2. Learning Objectives

• Understand the purpose of ResultSet in JDBC.
• Learn how to retrieve database records using SELECT queries.
• Understand cursor movement and the next() method.
• Retrieve column values using column names and indexes.
• Work with different Java and SQL data types.
• Use ResultSet with Statement and PreparedStatement.
• Understand ResultSet metadata and cursor types.
• Handle SQL NULL values and database exceptions.
• Manage JDBC resources using try-with-resources.
• Build a practical Java application for displaying database records.

3. What Is ResultSet?

ResultSet is a JDBC interface that provides access to the rows returned by a database query. It maintains a cursor that identifies the current row being processed.

When a ResultSet is first created, its cursor is positioned before the first row. The next() method moves the cursor to the next row and returns true if a row is available. When no additional rows exist, it returns false.

Basic Syntax

Statement statement = connection.createStatement();

ResultSet resultSet = statement.executeQuery(
    "SELECT id, name FROM students"
);

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");

    System.out.println(id + " " + name);
}

In this example, the SELECT query retrieves student IDs and names. The while loop processes every row returned by the database.

4. Understanding the JDBC Result Retrieval Process

Retrieving records involves several steps, beginning with a database connection and ending with processing the returned rows.

1. Establish a connection: Use DriverManager or a configured DataSource to obtain a Connection.
2. Create a SQL statement: Use Statement or PreparedStatement.
3. Execute the query: Call executeQuery() for a SELECT statement.
4. Receive the ResultSet: The query returns the matching records.
5. Move through rows: Use next() to advance the cursor.
6. Retrieve values: Use getter methods such as getInt() and getString().
7. Close resources: Release the ResultSet, statement, and connection when finished.

JDBC Retrieval Flow

Java Application
       |
       v
Establish Database Connection
       |
       v
Create Statement / PreparedStatement
       |
       v
Execute SELECT Query
       |
       v
Receive ResultSet
       |
       v
Move Cursor Using next()
       |
       v
Read Column Values
       |
       v
Display or Process Records
       |
       v
Close JDBC Resources

5. Preparing the Sample Database

The following examples use MySQL and a database named college_db. Create the database and students table before executing the Java programs.

CREATE DATABASE college_db;

USE college_db;

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100),
    marks DECIMAL(5,2)
);

INSERT INTO students (name, course, marks)
VALUES
('Amit', 'Java', 85.50),
('Priya', 'Python', 91.00),
('Rahul', 'SQL', 78.00),
('Neha', 'Advanced Java', 88.50);

The table stores each student's ID, name, course, and marks. The ID is generated automatically when a new record is inserted without specifying the ID.

6. Establishing a Database Connection

Before retrieving records, the Java application must establish a connection to the database. The following example uses JDBC with MySQL Connector/J.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password)) {

            System.out.println(
                "Database connected successfully."
            );

        } catch (SQLException e) {
            System.err.println(
                "Connection failed: " + e.getMessage()
            );
        }
    }
}

Replace the example credentials with valid database credentials. Ensure that MySQL is running and that the JDBC driver is available in the project's dependencies.

7. Creating a ResultSet Using Statement

The Statement interface executes SQL statements. When a SELECT query is executed through executeQuery(), JDBC returns a ResultSet containing the query results.

Syntax

Statement statement = connection.createStatement();

ResultSet resultSet =
    statement.executeQuery("SELECT * FROM students");

Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RetrieveStudents {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        String sql =
            "SELECT id, name, course, marks FROM students";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             Statement statement =
                 connection.createStatement();
             ResultSet resultSet =
                 statement.executeQuery(sql)) {

            while (resultSet.next()) {
                System.out.println(
                    resultSet.getInt("id") + " | " +
                    resultSet.getString("name") + " | " +
                    resultSet.getString("course") + " | " +
                    resultSet.getBigDecimal("marks")
                );
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

• createStatement() creates a Statement object.
• executeQuery(sql) executes the SELECT query.
• ResultSet represents the rows returned by the database.
• next() advances the cursor to the next available row.
• Getter methods retrieve values from the current row.
• Try-with-resources closes the JDBC resources automatically.

8. Understanding the ResultSet Cursor

A cursor is a pointer that identifies the current position within a ResultSet. Understanding cursor movement is important because values can only be retrieved from a valid current row.

For a typical forward-only ResultSet, the cursor initially sits before the first row. Each call to next() advances it by one row.

Example

while (resultSet.next()) {
    String studentName =
        resultSet.getString("name");

    System.out.println(studentName);
}

The loop continues until all available records have been processed. A forward-only ResultSet normally cannot move backward or revisit earlier rows.

9. Retrieving Column Values Using Column Names

ResultSet provides getter methods that retrieve column values using column labels. This approach often makes the code easier to read because the column's meaning is explicit.

Example

while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    String course = resultSet.getString("course");
    java.math.BigDecimal marks =
        resultSet.getBigDecimal("marks");

    System.out.println("Student ID: " + id);
    System.out.println("Name: " + name);
    System.out.println("Course: " + course);
    System.out.println("Marks: " + marks);
}

The column label may be the original column name or a label defined with an SQL alias. For example, if a query uses marks AS student_marks, the result can be retrieved using getBigDecimal("student_marks").

10. Retrieving Column Values Using Column Indexes

ResultSet also allows values to be retrieved by column index. JDBC column indexes start at 1, not 0.

Example

String sql =
    "SELECT id, name, course, marks FROM students";

try (Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {

    while (resultSet.next()) {
        int id = resultSet.getInt(1);
        String name = resultSet.getString(2);
        String course = resultSet.getString(3);
        java.math.BigDecimal marks =
            resultSet.getBigDecimal(4);

        System.out.println(
            id + " | " + name + " | " +
            course + " | " + marks
        );
    }
}

Index-based access can be useful in some situations, but it depends on the order of columns in the SELECT list. If the query's column order changes, index-based code may retrieve incorrect values. Column labels are often easier to maintain.

11. Common ResultSet Getter Methods

Getter methods convert SQL column values into corresponding Java data types where supported by the JDBC driver.

Choose a getter that is compatible with the SQL column type. For exact decimal values, BigDecimal is often preferable to double because binary floating-point types can introduce rounding differences.

12. Retrieving Selected Records Using WHERE

The WHERE clause filters records so that the database returns only the rows matching a specified condition.

Example: Retrieve Students with Marks Above 80

String sql =
    "SELECT id, name, marks FROM students " +
    "WHERE marks > 80";

try (Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {

    while (resultSet.next()) {
        System.out.println(
            resultSet.getInt("id") + " | " +
            resultSet.getString("name") + " | " +
            resultSet.getBigDecimal("marks")
        );
    }
}

The database filters the records before returning the ResultSet. Only students whose marks exceed 80 are included.

13. Retrieving Records Using PreparedStatement

PreparedStatement is recommended when query conditions depend on user input or application values. It allows parameter values to be supplied separately from the SQL statement.

Example: Search by Student ID

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SearchStudentById {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";
        int studentId = 1;

        String sql =
            "SELECT id, name, course, marks " +
            "FROM students WHERE id = ?";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 connection.prepareStatement(sql)) {

            ps.setInt(1, studentId);

            try (ResultSet resultSet = ps.executeQuery()) {
                if (resultSet.next()) {
                    System.out.println(
                        "Name: " +
                        resultSet.getString("name")
                    );
                    System.out.println(
                        "Course: " +
                        resultSet.getString("course")
                    );
                    System.out.println(
                        "Marks: " +
                        resultSet.getBigDecimal("marks")
                    );
                } else {
                    System.out.println(
                        "No student found for the given ID."
                    );
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PreparedStatement helps protect against SQL injection when parameter values are bound correctly. It is the preferred option for queries involving external or user-supplied values.

14. Checking Whether Records Exist

Sometimes an application needs to know whether a matching record exists rather than retrieving every column. A SELECT query with a suitable condition can be used for this purpose.

Example

String sql =
    "SELECT id FROM students WHERE id = ?";

try (PreparedStatement ps =
         connection.prepareStatement(sql)) {

    ps.setInt(1, 2);

    try (ResultSet resultSet = ps.executeQuery()) {
        if (resultSet.next()) {
            System.out.println(
                "Student record exists."
            );
        } else {
            System.out.println(
                "Student record does not exist."
            );
        }
    }
}

The next() method returns true when a matching row is available. Selecting only the required column can make the query more focused.

15. Retrieving Aggregate Results

SQL aggregate functions calculate summary values such as counts, averages, totals, minimums, and maximums. Even when a query calculates a summary instead of returning individual records, its result is commonly read through ResultSet.

Example: Count Students

String sql = "SELECT COUNT(*) AS total_students FROM students";

try (Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {

    if (resultSet.next()) {
        int total =
            resultSet.getInt("total_students");

        System.out.println(
            "Total students: " + total
        );
    }
}

Example: Calculate Average Marks

String sql =
    "SELECT AVG(marks) AS average_marks FROM students";

try (Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {

    if (resultSet.next()) {
        java.math.BigDecimal average =
            resultSet.getBigDecimal("average_marks");

        System.out.println(
            "Average marks: " + average
        );
    }
}

Aggregate queries without GROUP BY generally return one row, even when the source table contains no matching records. In that case, COUNT() returns zero, while aggregates such as AVG() may return SQL NULL.

16. Handling SQL NULL Values

Database columns may contain SQL NULL, which represents a missing or unknown value. Java getter methods do not always preserve this distinction when returning primitive types.

For example, getInt() returns 0 when the SQL value is NULL. The application can call wasNull() immediately after the getter to determine whether the last retrieved value was SQL NULL.

Example

int marks = resultSet.getInt("marks");

if (resultSet.wasNull()) {
    System.out.println("Marks are not available.");
} else {
    System.out.println("Marks: " + marks);
}

For nullable reference types, such as String and BigDecimal, the getter generally returns null when the database value is SQL NULL. Check for null before using the value.

17. Using ResultSetMetaData

ResultSetMetaData provides information about the columns returned by a query. It can be used to inspect column names, data types, and the number of columns.

Example

import java.sql.ResultSetMetaData;

ResultSetMetaData metadata = resultSet.getMetaData();

int columnCount = metadata.getColumnCount();

for (int i = 1; i <= columnCount; i++) {
    System.out.println(
        "Column " + i + ": " +
        metadata.getColumnLabel(i) +
        " | Type: " +
        metadata.getColumnTypeName(i)
    );
}

Important Methods

Metadata is especially useful for generic reporting tools, database inspection utilities, and applications that need to display columns without hardcoding every field.

18. ResultSet Cursor Types

JDBC supports different ResultSet cursor types. The default ResultSet is typically forward-only and read-only, meaning it moves forward through rows and does not directly update the underlying database.

Creating a Scrollable ResultSet

try (Statement statement =
         connection.createStatement(
             ResultSet.TYPE_SCROLL_INSENSITIVE,
             ResultSet.CONCUR_READ_ONLY);
     ResultSet resultSet =
         statement.executeQuery(
             "SELECT id, name FROM students")) {

    if (resultSet.last()) {
        System.out.println(
            "Last student: " +
            resultSet.getString("name")
        );
    }

    if (resultSet.first()) {
        System.out.println(
            "First student: " +
            resultSet.getString("name")
        );
    }
}

This example requests a scrollable, read-only ResultSet. Actual support depends on the JDBC driver and database. If the requested type is unsupported, the driver may reject it or use supported behavior, so verify the returned type when necessary.

19. Important ResultSet Cursor Methods

Methods such as previous(), first(), last(), and absolute() are not available for ordinary forward-only ResultSets. Use them only when the ResultSet type supports scrolling.

20. ResultSet and Resource Management

ResultSet, Statement, and Connection objects use resources that should be released when database processing is complete. Try-with-resources is the recommended approach because it closes resources automatically, including when an exception occurs.

Recommended Example

String sql = "SELECT id, name FROM students";

try (Connection connection =
         DriverManager.getConnection(
             url, username, password);
     PreparedStatement ps =
         connection.prepareStatement(sql);
     ResultSet resultSet = ps.executeQuery()) {

    while (resultSet.next()) {
        System.out.println(
            resultSet.getInt("id") + " - " +
            resultSet.getString("name")
        );
    }

} catch (SQLException e) {
    System.err.println(
        "Unable to retrieve records: " +
        e.getMessage()
    );
}

The resources are closed automatically in reverse order: ResultSet, PreparedStatement, and Connection. Closing the statement also closes its current ResultSet, but declaring each resource explicitly helps keep ownership and cleanup clear.

21. Common Errors and Troubleshooting

22. Best Practices for Using ResultSet

• Always call next() before reading the first row.
• Use column labels for clearer and more maintainable code.
• Select only the columns needed by the application.
• Use PreparedStatement for queries containing dynamic values.
• Choose getter methods that match the SQL data types.
• Handle SQL NULL values appropriately.
• Use try-with-resources to release JDBC resources.
• Do not assume every JDBC driver supports scrollable or updatable ResultSets.
• For large datasets, consider pagination, appropriate fetch sizes, and database-side filtering.
• Handle SQLException without exposing sensitive database details to end users.
• Use BigDecimal when precision is important for decimal values.

23. Practical Project: Student Record Viewer

Create a Java console application that retrieves student records from MySQL and displays them in a readable format.

Project Requirements

• Connect to the college_db database.
• Retrieve all student records using a SELECT query.
• Display student ID, name, course, and marks.
• Search for a student using an ID.
• Display an appropriate message when no matching student exists.
• Handle database exceptions.
• Close all JDBC resources safely.

Complete Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentRecordViewer {
    public static void main(String[] args) {
        String url =
            "jdbc:mysql://localhost:3306/college_db";
        String username = "your_username";
        String password = "your_password";

        String sql =
            "SELECT id, name, course, marks " +
            "FROM students ORDER BY id";

        try (Connection connection =
                 DriverManager.getConnection(
                     url, username, password);
             PreparedStatement ps =
                 connection.prepareStatement(sql);
             ResultSet resultSet = ps.executeQuery()) {

            System.out.println(
                "ID | Name | Course | Marks"
            );
            System.out.println(
                "--------------------------------"
            );

            boolean found = false;

            while (resultSet.next()) {
                found = true;

                int id = resultSet.getInt("id");
                String name =
                    resultSet.getString("name");
                String course =
                    resultSet.getString("course");
                java.math.BigDecimal marks =
                    resultSet.getBigDecimal("marks");

                System.out.println(
                    id + " | " + name + " | " +
                    course + " | " + marks
                );
            }

            if (!found) {
                System.out.println(
                    "No student records found."
                );
            }

        } catch (SQLException e) {
            System.err.println(
                "Database error: " + e.getMessage()
            );
        }
    }
}

Expected Output

ID | Name | Course | Marks
--------------------------------
1 | Amit | Java | 85.50
2 | Priya | Python | 91.00
3 | Rahul | SQL | 78.00
4 | Neha | Advanced Java | 88.50

The displayed IDs and records depend on the actual contents of the database. The example assumes that the sample records have been inserted and that no additional records have changed the results.

24. Practice Exercises

1. Retrieve and display all records from the students table.
2. Display only the name and course columns.
3. Retrieve students whose marks are greater than 85.
4. Sort student records by name in ascending order.
5. Search for a student using PreparedStatement and an ID parameter.
6. Display the total number of students using COUNT().
7. Calculate average marks using AVG().
8. Use ResultSetMetaData to print the column names.
9. Handle a query that returns no matching rows.
10. Retrieve a nullable column and handle SQL NULL correctly.
11. Create a scrollable ResultSet if supported by the JDBC driver.
12. Build a simple student search application with database error handling.

25. Interview Questions and Answers

Q1. What is ResultSet in JDBC?

Answer: ResultSet is an interface that represents the tabular data returned by executing a database query.

Q2. Which method is used to move to the next row?

Answer: The next() method advances the cursor and returns true if a row is available.

Q3. Where is the cursor positioned when a ResultSet is created?

Answer: The cursor is initially positioned before the first row.

Q4. How can you retrieve a column value?

Answer: Use an appropriate getter method, such as getInt(), getString(), or getBigDecimal(), with a column label or index.

Q5. Do ResultSet column indexes start at zero?

Answer: No. JDBC column indexes start at 1.

Q6. What is the difference between Statement and ResultSet?

Answer: Statement executes SQL commands, while ResultSet provides access to rows returned by a query.

Q7. What is the difference between executeQuery() and executeUpdate()?

Answer: executeQuery() returns a ResultSet for queries that return rows. executeUpdate() returns an update count for operations such as INSERT, UPDATE, DELETE, and applicable DDL statements.

Q8. What is ResultSetMetaData?

Answer: It provides information about the columns in a ResultSet, including column count, labels, and data types.

Q9. Can a ResultSet move backward?

Answer: Only if it supports scrolling. The default ResultSet is typically forward-only.

Q10. How should JDBC resources be closed?

Answer: Use try-with-resources to automatically close ResultSet, Statement or PreparedStatement, and Connection objects.

Q11. How can SQL NULL be detected after getInt()?

Answer: Call wasNull() immediately after getInt() to check whether the last retrieved SQL value was NULL.

Q12. Why is PreparedStatement recommended for dynamic queries?

Answer: It supports parameter binding and helps prevent SQL injection when untrusted values are supplied as parameters.

Q13. Can ResultSet retrieve aggregate results?

Answer: Yes. Values returned by aggregate queries such as COUNT(), SUM(), and AVG() can be retrieved through ResultSet getter methods.

Q14. Is every ResultSet updatable?

Answer: No. The default ResultSet is generally read-only. Updatable ResultSets require appropriate cursor settings and driver support.

26. Quick Revision Checklist

• ResultSet belongs to the java.sql package.
• It stores the results returned by a database query.
• Use executeQuery() to obtain a ResultSet from a SELECT query.
• Call next() before retrieving the first row.
• Use getter methods to retrieve column values.
• Column indexes start at 1.
• Use ResultSetMetaData to inspect returned columns.
• Handle SQL NULL values carefully.
• Use PreparedStatement for parameterized queries.
• Use try-with-resources to close JDBC resources.
• Check driver support before using scrollable ResultSet methods.

27. Conclusion

ResultSet is a fundamental JDBC interface for retrieving and processing records returned by SQL queries. By using cursor navigation, getter methods, and ResultSetMetaData, Java developers can access database records and integrate them into application logic.

Understanding ResultSet alongside Connection, Statement, and PreparedStatement is essential for building reliable database-driven Java applications. Following best practices for resource management, data types, NULL values, and parameterized queries helps make JDBC code safer and easier to maintain.

To continue learning enterprise Java development, explore the Advanced Java Training Course at JustAcademy. You can also register for a course demo or visit the JustAcademy official website.